package com.sh.data.engine.domain.dataquality.service.impl;

import cn.hutool.core.convert.Convert;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.google.common.collect.Lists;
import com.sh.data.engine.common.enumDefinition.DataQualityCategoryEnum;
import com.sh.data.engine.common.enumDefinition.DataQualityRuleCompareTypeEnum;
import com.sh.data.engine.common.exception.BusinessException;
import com.sh.data.engine.common.util.DateUtil;
import com.sh.data.engine.domain.base.model.PageResult;
import com.sh.data.engine.domain.dataasset.model.domain.TableInfoBizDomain;
import com.sh.data.engine.domain.dataasset.model.param.QueryTableParam;
import com.sh.data.engine.domain.dataasset.service.TableService;
import com.sh.data.engine.domain.dataasset.service.TblPropService;
import com.sh.data.engine.domain.dataquality.model.domain.*;
import com.sh.data.engine.domain.dataquality.model.param.DataQualityAddParExpressionParam;
import com.sh.data.engine.domain.dataquality.model.param.DataQualityRuleParam;
import com.sh.data.engine.domain.dataquality.model.param.SaveDataQualityScheduleParam;
import com.sh.data.engine.domain.dataquality.service.*;
import com.sh.data.engine.domain.integration.datasource.service.DataSourceService;
import com.sh.data.engine.domain.shims.db.model.FieldInfoDomain;
import com.sh.data.engine.domain.util.ConvertUtil;
import com.sh.data.engine.domain.workspace.manager.model.domain.UserDomain;
import com.sh.data.engine.domain.workspace.manager.service.UserService;
import com.sh.data.engine.job.admin.core.model.XxlJobInfo;
import com.sh.data.engine.job.admin.core.route.ExecutorRouteStrategyEnum;
import com.sh.data.engine.job.admin.core.scheduler.MisfireStrategyEnum;
import com.sh.data.engine.job.admin.core.scheduler.ScheduleTypeEnum;
import com.sh.data.engine.job.admin.service.XxlJobService;
import com.sh.data.engine.job.core.biz.model.ReturnT;
import com.sh.data.engine.job.core.enums.ExecutorBlockStrategyEnum;
import com.sh.data.engine.job.core.glue.GlueTypeEnum;
import com.sh.data.engine.repository.dao.dataasset.entity.TblPropEntity;
import com.sh.data.engine.repository.dao.dataquality.entity.DataQualityRuleEntity;
import com.sh.data.engine.repository.dao.dataquality.entity.DataQualityRuleTemplateEntity;
import com.sh.data.engine.repository.dao.dataquality.entity.DataQualityScheduleEntity;
import com.sh.data.engine.repository.dao.dataquality.entity.DataQualityTablePartitionEntity;
import com.sh.data.engine.repository.dao.integration.offline.entity.OfflineSyncEntity;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author tinglan.ys
 * @description:
 * @date 2022/2/15 下午6:13
 */
@Service
@Slf4j
public class DataQualityManagerServiceImpl implements DataQualityManagerService {

    @Autowired
    private DataQualityRuleService dataQualityRuleService;

    @Autowired
    private DataQualityTablePartitionService dataQualityTablePartitionService;

    @Autowired
    private UserService userService;

    @Autowired
    private DataQualityRuleTemplateService dataQualityRuleTemplateService;

    @Autowired
    private DataQualityScheduleService dataQualityScheduleService;

    @Autowired
    private TableService tableService;

    @Autowired
    private DataQualityService dataQualityService;

    @Autowired
    private TblPropService tblPropService;

    @Autowired
    private XxlJobService xxlJobService;

    @Autowired
    private DataSourceService dataSourceService;

    @Override
    public PageResult<DataQualityTableInfoDomain> getTables(
        Long projectId,
        List<Long> themeIdList,
        Integer pageNum,
        Integer pageSize,
        String tableName,
        String order) {

        // tableService.getTableList() 和 tableService.getTablePage() 性能上没有区别，
        QueryTableParam queryTableParam = new QueryTableParam();
        queryTableParam.setThemeIdList(themeIdList);
        List<TableInfoBizDomain> tableList = tableService.getTableList(projectId, queryTableParam);

        // 根据tableName 过滤
        List<TableInfoBizDomain> tableInfoDomainList = filterByTableNameOrComment(tableList, tableName);

        // 拼入规则条数信息
        List<Long> tblIds =
            tableInfoDomainList.stream().map(d -> d.getTblId()).collect(Collectors.toList());

        List<DataQualityTblRuleCountDomain> tblRuleCountList = getTblRuleCount(tblIds);
        Map<Long, Integer> tblRuleCountMap = new HashMap<>();
        if (CollectionUtils.isNotEmpty(tblRuleCountList)) {
            tblRuleCountMap =
                tblRuleCountList.stream()
                    .collect(Collectors.toMap(x -> x.getTblId(), x -> x.getRuleCount()));
        }

        List<DataQualityTableInfoDomain> dataQualityTableInfoDomains = Lists.newArrayList();

        for (TableInfoBizDomain t : tableInfoDomainList) {
            DataQualityTableInfoDomain domain = new DataQualityTableInfoDomain();
            domain.setTblId(t.getTblId());
            domain.setTblIdStr(t.getTblIdStr());
            domain.setTableName(t.getTableName());
            domain.setComment(t.getTableComment());
            domain.setSchema(t.getSchema());
            domain.setRuleNum(tblRuleCountMap.getOrDefault(t.getTblId(), 0));
            domain.setHasPartition(t.isPartitionTable() ? 1 : 0);
            domain.setThemeId(t.getThemeId());
            // 设置这个表的权限 0-只读 1—读写 2-无读写权限 如果无读写权限，那么不能建立规则。注释了，建立规则的时候去判断是否有权限
            dataQualityTableInfoDomains.add(domain);
        }

        // 排序
        dataQualityTableInfoDomains.sort(
            (o1, o2) -> {
                if (StringUtils.equalsIgnoreCase(order, "asc")) {
                    return o1.getRuleNum().compareTo(o2.getRuleNum());
                } else {
                    return o2.getRuleNum().compareTo(o1.getRuleNum());
                }
            });

        // 分页
        PageResult pageResult;
        if (pageNum != null && pageSize != null) {
            pageResult = new PageResult(dataQualityTableInfoDomains, pageNum, pageSize);
        } else {
            pageResult = new PageResult(dataQualityTableInfoDomains);
        }
        return pageResult;
    }

    private List<TableInfoBizDomain> filterByTableNameOrComment(
        List<TableInfoBizDomain> tableInfoDomains, String searchStr) {

        if (StringUtils.isBlank(searchStr)) {
            return tableInfoDomains;
        }

        if (CollectionUtils.isEmpty(tableInfoDomains)) {
            return Collections.emptyList();
        }
        final String searchStr2 = StringUtils.trim(searchStr);

        return tableInfoDomains.stream()
            .filter(Objects::nonNull)
            .filter(
                e ->
                    StringUtils.containsIgnoreCase(e.getTableName(), searchStr2)
                        || StringUtils.containsIgnoreCase(e.getTableComment(), searchStr2))
            .collect(Collectors.toList());
    }

    @Override
    public List<DataQualityTblRuleCountDomain> getTblRuleCount(List<Long> tblIds) {

    /*    select
    tbl_id as tblId, count(*) as ruleCount
    from dataquality_rule where tbl_id in
        <foreach collection="tblIds" index="index" item="tblId" open="(" separator="," close=")">
            #{tblId}
        </foreach>
    GROUP BY tbl_id*/

        QueryWrapper<DataQualityRuleEntity> qw = new QueryWrapper<>();
        qw.select("tbl_id as tblId, count(*) as ruleCount");
        qw.in(CollectionUtils.isNotEmpty(tblIds), "tbl_id", tblIds);
        qw.groupBy("tbl_id");

        List<Map<String, Object>> maps = dataQualityRuleService.listMaps(qw);

        List<DataQualityTblRuleCountDomain> mappedResults = new ArrayList<>();
        for (Map<String, Object> map : maps) {
            DataQualityTblRuleCountDomain domain = new DataQualityTblRuleCountDomain();
            domain.setTblId(Convert.toLong(map.get("tblid")));
            domain.setRuleCount(Convert.toInt(map.get("rulecount")));
            mappedResults.add(domain);
        }

        return mappedResults;
    }

    /**
     * @param request
     * @param projectId
     * @param userId
     * @description : 添加or编辑分区表达式
     * @author : tinglan.ys
     * @date : 2022/2/18
     */
    @Override
    public Boolean addParExpression(
        DataQualityAddParExpressionParam request, Long projectId, String userId) {

        String parExpression = request.getParExpression().trim();

        long count =
            dataQualityTablePartitionService.count(
                new LambdaQueryWrapper<DataQualityTablePartitionEntity>()
                    .eq(DataQualityTablePartitionEntity::getProjectId, projectId)
                    .eq(DataQualityTablePartitionEntity::getTblId, request.getTblId())
                    .eq(DataQualityTablePartitionEntity::getPartitionExpress, parExpression)
                    .ne(
                        request.getId() != null,
                        DataQualityTablePartitionEntity::getId,
                        request.getId()));

        if (count > 0) {
            throw new BusinessException("已存在相同的表达式");
        }

        DataQualityTablePartitionEntity tablePartition = new DataQualityTablePartitionEntity();
        if (null == request.getId()) {
            tablePartition.setProjectId(projectId);
            tablePartition.setTblId(request.getTblId());
            tablePartition.setPartitionExpress(parExpression);
            tablePartition.setTblName(request.getTableName());
            tablePartition.setUserId(userId);
            tablePartition.setCreateTime(new Date());
            tablePartition.setUpdateTime(new Date());
            dataQualityTablePartitionService.save(tablePartition);
        } else {

            tablePartition.setId(request.getId());
            tablePartition.setTblId(request.getTblId());
            tablePartition.setTblName(request.getTableName());
            tablePartition.setPartitionExpress(parExpression);
            tablePartition.setUpdateTime(new Date());
            tablePartition.setUserId(userId);
            dataQualityTablePartitionService.updateById(tablePartition);
        }

        return true;
    }

    /**
     * @param tblId
     * @param tableName
     * @param hasPartition
     * @param parExpression
     * @param projectId
     * @param userId
     * @description : 获取表达式列表
     * @author : tinglan.ys
     * @date : 2022/2/18
     */
    @Override
    public List<DataQualityTablePartitionDomain> getParExpressions(
        Long tblId,
        String tableName,
        Integer hasPartition,
        String parExpression,
        Long projectId,
        String userId) {
        // 若表无分区(只有hive才存在分区)，则自动生成一个NOPARTITIONTABLE表达式
        if (hasPartition == 0) {
            DataQualityTablePartitionEntity tablePartition = new DataQualityTablePartitionEntity();
            tablePartition.setProjectId(projectId);
            tablePartition.setTblId(tblId);

            List<DataQualityTablePartitionEntity> tablePartitions =
                dataQualityTablePartitionService.list(
                    new LambdaQueryWrapper<DataQualityTablePartitionEntity>()
                        .eq(DataQualityTablePartitionEntity::getProjectId, projectId)
                        .eq(DataQualityTablePartitionEntity::getTblId, tblId));

            if (CollectionUtils.isEmpty(tablePartitions)) {
                tablePartition.setUserId(userId);
                tablePartition.setTblName(tableName);
                tablePartition.setPartitionExpress("NOPARTITIONTABLE");
                tablePartition.setCreateTime(new Date());
                tablePartition.setUpdateTime(new Date());
                dataQualityTablePartitionService.save(tablePartition);
            }
        }

        List<DataQualityTablePartitionEntity> multi =
            dataQualityTablePartitionService.list(
                new LambdaQueryWrapper<DataQualityTablePartitionEntity>()
                    .eq(DataQualityTablePartitionEntity::getProjectId, projectId)
                    .eq(DataQualityTablePartitionEntity::getTblId, tblId)
                    .like(
                        StringUtils.isNotBlank(parExpression),
                        DataQualityTablePartitionEntity::getPartitionExpress,
                        parExpression));

        List<DataQualityTablePartitionDomain> dataQualityTablePartitionDomains =
            ConvertUtil.copyProperties(multi, DataQualityTablePartitionDomain.class);

        return dataQualityTablePartitionDomains;
    }

    @Override
    public Boolean deleteParExpression(Long id) {

        long count =
            dataQualityRuleService.count(
                new LambdaQueryWrapper<DataQualityRuleEntity>()
                    .eq(DataQualityRuleEntity::getPartitionId, id));

        if (count > 0) {
            throw new BusinessException("该表达式下有规则，不允许删除");
        }

        dataQualityTablePartitionService.removeById(id);
        return true;
    }

    @Override
    public PageResult<DataQualityRuleDomain> rules(
        Long tblId, Integer ruleDiy, Long parExpressionId, Integer pageNum, Integer pageSize) {

        List<DataQualityRuleEntity> rules =
            dataQualityRuleService.list(
                new LambdaQueryWrapper<DataQualityRuleEntity>()
                    .eq(DataQualityRuleEntity::getTblId, tblId)
                    .eq(ruleDiy != null, DataQualityRuleEntity::getRuleDiy, ruleDiy)
                    .eq(parExpressionId != null, DataQualityRuleEntity::getPartitionId, parExpressionId)
                    .orderByDesc(DataQualityRuleEntity::getUpdateTime));

        List<DataQualityRuleDomain> responses = new ArrayList<>();

        if (CollectionUtils.isNotEmpty(rules)) {
            for (DataQualityRuleEntity rule : rules) {
                DataQualityRuleDomain response = new DataQualityRuleDomain();
                response.setId(rule.getId());
                response.setRuleName(rule.getRuleName());
                response.setFieldNames(rule.getFieldNames());
                if (rule.getTemplateId() != null) {
                    // 如果有模版id，塞入模版规则名
                    DataQualityRuleTemplateEntity byId =
                        dataQualityRuleTemplateService.getById(rule.getTemplateId());
                    String templateName = byId.getName();
                    response.setTemplateName(templateName);
                }

                response.setRuleStrength(rule.getRuleStrength());

                List<String> compareTypeList = new ArrayList<>();
                if (rule.getRuleCompareType() != null && !"null".equals(rule.getRuleCompareType())) {
                    String[] ruleCompareTypeList = rule.getRuleCompareType().split(",");
                    for (String ruleCompareType : ruleCompareTypeList) {
                        DataQualityRuleCompareTypeEnum compareType =
                            DataQualityRuleCompareTypeEnum.getByCompareType(Integer.valueOf(ruleCompareType));
                        compareTypeList.add(compareType == null ? null : compareType.getDesc());
                    }
                }
                response.setCompareType(StringUtils.join(compareTypeList.toArray(), ","));
                response.setStatisticalModel(rule.getRuleDiyStatisticalModel());
                response.setExpectValue(
                    "null".equals(rule.getRuleExpectValue()) ? null : rule.getRuleExpectValue());
                response.setCreateUsername(userService.getUserById(rule.getCreatorId()).getUserName());
                response.setCreateTime(rule.getCreateTime());
                response.setUpdateUsername(userService.getUserById(rule.getUpdaterId()).getUserName());
                response.setUpdateTime(rule.getUpdateTime());
                responses.add(response);
            }
        }

        return new PageResult<>(responses, pageNum, pageSize);
    }

    //    @BizDataIndexAnnotation(
//        type = BizDataIndexTypeEnum.DATA_QUALITY,
//        dataIdFieldName = "id",
//        dataIdParamIndex = 0,
//        operateType = BizDataIndexAnnotation.OperateType.SAVE)
    @Override
    public Boolean saveRule(
        DataQualityRuleParam dataQualityRuleParam, Long projectId, UserDomain userInfo) {

        DataQualityRuleEntity dataQualityRule =
            ConvertUtil.copyProperties(dataQualityRuleParam, DataQualityRuleEntity.class);

        Long count =
            dataQualityRuleService.count(
                new LambdaQueryWrapper<DataQualityRuleEntity>()
                    .eq(DataQualityRuleEntity::getPartitionId, dataQualityRule.getPartitionId())
                    .eq(DataQualityRuleEntity::getRuleName, dataQualityRule.getRuleName())
                    .ne(
                        null != dataQualityRule.getId(),
                        DataQualityRuleEntity::getId,
                        dataQualityRule.getId()));

        if (count > 0) {
            throw new BusinessException("规则名称不能重复");
        }

        if (dataQualityRule.getRuleDiy() == 0) {
            DataQualityRuleTemplateEntity template =
                dataQualityRuleTemplateService.getById(dataQualityRule.getTemplateId());
            if (null == template) {
                throw new BusinessException("规则模版不存在");
            }
            dataQualityRule.setTemplateCategory(template.getCategory());
        }

        dataQualityRule.setUpdaterId(userInfo.getId());
        dataQualityRule.setUpdateUsername(userInfo.getUserName());
        dataQualityRule.setUpdateTime(new Date());

        if (null == dataQualityRule.getId()) {
            // 新增逻辑
            dataQualityRule.setProjectId(projectId);
            dataQualityRule.setCreatorId(userInfo.getId());
            dataQualityRule.setCreateUsername(userInfo.getUserName());
            dataQualityRule.setCreateTime(new Date());
            dataQualityRuleService.save(dataQualityRule);

            dataQualityRuleParam.setId(dataQualityRule.getId());
        } else {
            DataQualityRuleEntity dataQualityRuleEntity =
                dataQualityRuleService.getById(dataQualityRule.getId());
            if (dataQualityRuleEntity.getTemplateId() != null
                && dataQualityRule.getTemplateId() != null) {
                if (!dataQualityRuleEntity.getTemplateId().equals(dataQualityRule.getTemplateId())) {
                    throw new BusinessException("编辑时无法修改-规则模版-");
                }
            }
            dataQualityRuleService.updateById(dataQualityRule);
        }
        return true;
    }

    //    @BizDataIndexAnnotation(
//        type = BizDataIndexTypeEnum.DATA_QUALITY,
//        dataIdFieldName = "id",
//        dataIdParamIndex = 0,
//        operateType = BizDataIndexAnnotation.OperateType.DELETE)
    @Override
    public Boolean deleteRule(Long id) {
        DataQualityRuleEntity dataqualityRuleEntity = dataQualityRuleService.getById(id);
        if (dataqualityRuleEntity == null) {
            return true;
        }
        dataQualityRuleService.removeById(id);

        return true;
    }

    @Override
    public DataQualityRuleDetailDomain getRule(Long id) {
        DataQualityRuleEntity rule = dataQualityRuleService.getById(id);

        DataQualityRuleDetailDomain response = new DataQualityRuleDetailDomain();

        if (null != rule) {
            BeanUtils.copyProperties(rule, response);

            if (rule.getRuleDiy() == 0) {

                DataQualityRuleTemplateEntity ruleTemplate =
                    dataQualityRuleTemplateService.getById(rule.getTemplateId());

                if (null != ruleTemplate) {
                    // 拼装版本名字
                    response.setTemplateName(ruleTemplate.getName());

                    //
                    if (StringUtils.isNotBlank(ruleTemplate.getConditionTemplate())) {
                        List<DataQualityRuleTemplateDomain.RuleCondition> ruleConditions =
                            JSON.parseArray(
                                ruleTemplate.getConditionTemplate(),
                                DataQualityRuleTemplateDomain.RuleCondition.class);
                        response.setRuleConditions(ruleConditions);
                    }

                    // 比较方式集合
                    if (StringUtils.isNotBlank(ruleTemplate.getCompareTypeTemplate())) {
                        List<DataQualityRuleTemplateDomain.CompareType> compareTypes =
                            JSON.parseArray(
                                ruleTemplate.getCompareTypeTemplate(),
                                DataQualityRuleTemplateDomain.CompareType.class);
                        response.setCompareTypes(compareTypes);
                    }

                    if (StringUtils.isNotBlank(ruleTemplate.getExpectValueTemplate())) {
                        DataQualityRuleTemplateDomain.ExpectValue expectValue =
                            JSON.parseObject(
                                ruleTemplate.getExpectValueTemplate(),
                                DataQualityRuleTemplateDomain.ExpectValue.class);
                        response.setExpectValue(expectValue);
                    }
                }
            }
        }

        return response;
    }

    @Override
    public List<DataQualityRuleTemplateDomain> getRuleTemplates(Integer templateTableType) {

        List<DataQualityRuleTemplateEntity> ruleTemplates =
            dataQualityRuleTemplateService.list(
                new LambdaQueryWrapper<DataQualityRuleTemplateEntity>()
                    .eq(DataQualityRuleTemplateEntity::getRowState, 1)
                    .eq(DataQualityRuleTemplateEntity::getTemplateTableType, templateTableType)
                    .orderByDesc(DataQualityRuleTemplateEntity::getName));

        List<DataQualityRuleTemplateDomain> responses = new ArrayList<>();

        if (CollectionUtils.isNotEmpty(ruleTemplates)) {
            for (DataQualityRuleTemplateEntity ruleTemplate : ruleTemplates) {
                DataQualityRuleTemplateDomain response = new DataQualityRuleTemplateDomain();
                BeanUtils.copyProperties(ruleTemplate, response);

                if (StringUtils.isNotBlank(ruleTemplate.getConditionTemplate())) {
                    List<DataQualityRuleTemplateDomain.RuleCondition> ruleConditions =
                        JSON.parseArray(
                            ruleTemplate.getConditionTemplate(),
                            DataQualityRuleTemplateDomain.RuleCondition.class);
                    response.setRuleConditions(ruleConditions);
                }

                if (StringUtils.isNotBlank(ruleTemplate.getCompareTypeTemplate())) {
                    List<DataQualityRuleTemplateDomain.CompareType> compareTypes =
                        JSON.parseArray(
                            ruleTemplate.getCompareTypeTemplate(),
                            DataQualityRuleTemplateDomain.CompareType.class);
                    response.setCompareTypes(compareTypes);
                }

                if (StringUtils.isNotBlank(ruleTemplate.getExpectValueTemplate())) {
                    DataQualityRuleTemplateDomain.ExpectValue expectValue =
                        JSON.parseObject(
                            ruleTemplate.getExpectValueTemplate(),
                            DataQualityRuleTemplateDomain.ExpectValue.class);
                    response.setExpectValue(expectValue);
                }

                responses.add(response);
            }
        }
        return responses;
    }

    @Override
    public DataQualityRuleTemplateDomain getRuleTemplateByTemplateId(Long templateId) {
        DataQualityRuleTemplateEntity ruleTemplate = dataQualityRuleTemplateService.getById(templateId);
        return ConvertUtil.copyProperties(ruleTemplate, DataQualityRuleTemplateDomain.class);
    }

    @Override
    public List<DataQualityTableFieldDomain> getTableFields(Long tblId, Long projectId) {
        TblPropEntity tblPropByTblId = tblPropService.getTblPropByTblId(projectId, tblId);

        //    List<FieldInfoDomain> tableField =
        //        tableManager.getTableField(
        //            projectId, tblPropByTblId.getSchema() + "." + tblPropByTblId.getTableName());

        String tableName = tblPropByTblId.getTableName();
        String schema = tblPropByTblId.getSchema();
        String fullTableName = tableName;
        if (StringUtils.isNotBlank(schema)) {
            fullTableName = schema + "." + tableName;
        }

        List<FieldInfoDomain> fieldListProjectId =
            dataSourceService.getFieldListProjectId(fullTableName, projectId);

        return ConvertUtil.copyProperties(fieldListProjectId, DataQualityTableFieldDomain.class);
    }

    @Override
    public DataQualityScheduleDomain getDataQualitySchedule(Long tblId, Long projectId) {

        DataQualityScheduleEntity schedule =
            dataQualityScheduleService.getOne(
                new LambdaQueryWrapper<DataQualityScheduleEntity>()
                    .eq(DataQualityScheduleEntity::getTblId, tblId)
                    .eq(DataQualityScheduleEntity::getProjectId, projectId));

        DataQualityScheduleDomain dataQualityScheduleDomain =
            ConvertUtil.copyProperties(schedule, DataQualityScheduleDomain.class);
        return dataQualityScheduleDomain;
    }

    @Override
    public Boolean saveDataQualitySchedule(SaveDataQualityScheduleParam request, Long projectId) {

        DataQualityScheduleEntity schedule =
            dataQualityScheduleService.getOne(
                new LambdaQueryWrapper<DataQualityScheduleEntity>()
                    .eq(DataQualityScheduleEntity::getTblId, request.getTblId())
                    .eq(DataQualityScheduleEntity::getProjectId, projectId));

        if (schedule == null) {
            // 设置调度周期
            schedule = new DataQualityScheduleEntity();
            schedule.setCycle(request.getCycle());
            schedule.setEffectiveDateEnd(DateUtil.getTodayEnd(request.getEffectiveDateEnd()));
            schedule.setEffectiveDateStart(request.getEffectiveDateStart());
            schedule.setEndTimeHour(request.getEndTimeHour());
            schedule.setEndTimeMin(request.getEndTimeMin());
            schedule.setExecPoint(request.getExecPoint());
            schedule.setStartTimeHour(request.getStartTimeHour());
            schedule.setStartTimeMin(request.getStartTimeMin());
            schedule.setStatus(request.getStatus());
            schedule.setProjectId(projectId);
            schedule.setTblId(request.getTblId());
            schedule.setCycleDependence(request.getCycleDependence());
            // 设置xxljob的调度
            String xxlJobId = saveXxlJob(request, projectId, request.getTblId().toString());
            schedule.setXxlJobId(xxlJobId);
            // 存入业务数据
            dataQualityScheduleService.save(schedule);
        } else {
            // 设置调度周期
            schedule.setCycle(request.getCycle());
            schedule.setEffectiveDateEnd(DateUtil.getTodayEnd(request.getEffectiveDateEnd()));
            schedule.setEffectiveDateStart(request.getEffectiveDateStart());
            schedule.setEndTimeHour(request.getEndTimeHour());
            schedule.setEndTimeMin(request.getEndTimeMin());
            schedule.setExecPoint(request.getExecPoint());
            schedule.setStartTimeHour(request.getStartTimeHour());
            schedule.setStartTimeMin(request.getStartTimeMin());
            schedule.setStatus(request.getStatus());
            schedule.setTblId(request.getTblId());
            schedule.setCycleDependence(request.getCycleDependence());
            // 设置xxlJob的调度 先删除原来的job然后新增一个job
            if (schedule.getXxlJobId() == null) {
                throw new BusinessException("数据质量任务不存在，请检查后重试！");
            }
            xxlJobService.remove(Integer.parseInt(schedule.getXxlJobId()));
            String xxlJobId = saveXxlJob(request, projectId, request.getTblId().toString());
            schedule.setXxlJobId(xxlJobId);
            dataQualityScheduleService.updateById(schedule);
        }
        return true;
    }

    private String saveXxlJob(SaveDataQualityScheduleParam request, Long projectId, String tblId) {
        OfflineSyncEntity.OfflineSyncSchedule xxlSchedule = new OfflineSyncEntity.OfflineSyncSchedule();
        xxlSchedule.setCycle(request.getCycle());
        xxlSchedule.setEffectiveDateEnd(DateUtil.getTodayEnd(request.getEffectiveDateEnd()));
        xxlSchedule.setEffectiveDateStart(request.getEffectiveDateStart());
        xxlSchedule.setEndTimeHour(String.valueOf(request.getEndTimeHour()));
        xxlSchedule.setEndTimeMin(String.valueOf(request.getEndTimeMin()));
        xxlSchedule.setExecPoint(request.getExecPoint());
        xxlSchedule.setStartTimeHour(String.valueOf(request.getStartTimeHour()));
        xxlSchedule.setStartTimeMin(String.valueOf(request.getStartTimeMin()));
        xxlSchedule.setStatus(request.getStatus());
        xxlSchedule.setCycleDependence(request.getCycleDependence());
        String jobDesc = "tblId为" + tblId + "的数据质量任务";
        String xxlJobId;
        try {
            xxlJobId = this.saveSyncTask(xxlSchedule, jobDesc, projectId, tblId);
        } catch (Exception e) {
            log.error("保存xxlJob任务异常", e);
            throw new BusinessException("保存xxlJob任务异常" + e.getMessage());
        }
        return xxlJobId;
    }

    /**
     * @param schedule
     * @description : 根据调度周期，保存调度
     * @author : tinglan.ys
     * @date : 2022/3/7
     */
    @Override
    public String saveSyncTask(
        OfflineSyncEntity.OfflineSyncSchedule schedule,
        String jobDesc,
        Long projectId,
        String tblId) {

        // 不用glue模式 使用bean模式了
        // String glueSource = this.generateDataQualityGlueSource(projectId, tblId);
        // 保存xxl-job
        XxlJobInfo xxlJobInfo = new XxlJobInfo();
        xxlJobInfo.setJobGroup(1);
        //
        xxlJobInfo.setJobDesc(jobDesc);
        xxlJobInfo.setAddTime(new Date());
        xxlJobInfo.setAuthor("ys");

        xxlJobInfo.setScheduleType(ScheduleTypeEnum.HUFU.name());
        xxlJobInfo.setScheduleConf(JSON.toJSONString(schedule));

        //  冻结状态 默认是停止状态0
        xxlJobInfo.setTriggerStatus(schedule.getStatus() == 1 ? 0 : 1);
        xxlJobInfo.setExecutorRouteStrategy(ExecutorRouteStrategyEnum.ROUND.name()); // 路由策略
        xxlJobInfo.setMisfireStrategy(MisfireStrategyEnum.DO_NOTHING.name()); // 调度过期策略
        xxlJobInfo.setExecutorBlockStrategy(ExecutorBlockStrategyEnum.DISCARD_LATER.name()); // 阻塞处理策略
        xxlJobInfo.setExecutorFailRetryCount(0); // 失败重试次数
        xxlJobInfo.setExecutorTimeout(0); // 任务超时时间
        xxlJobInfo.setGlueType(GlueTypeEnum.BEAN.name());
        xxlJobInfo.setExecutorHandler("DataQualityTask");
        // xxlJobInfo.setGlueSource(glueSource);
        xxlJobInfo.setGlueUpdatetime(new Date());
        xxlJobInfo.setGlueRemark(LocalDateTime.now().toString());
        ReturnT<String> add = xxlJobService.add(xxlJobInfo);
        return add.getContent();
    }

    private String generateDataQualityGlueSource(String projectId, String tblId) {
        String glueJavaFormat =
            "package com.xuelangyun.hufu.job.jobhandler.offline;\n"
                + "\n"
                + "import com.xuelangyun.hufu.job.core.handler.IJobHandler;\n"
                + "import com.xuelangyun.hufu.job.jobhandler.dataQuality.DataQualityTask;\n"
                + "import org.springframework.beans.factory.annotation.Autowired;\n"
                + "/**\n"
                + " * @author tinglan.ys\n"
                + " * @description:\n"
                + " * @date 2022/3/8 上午11:27\n"
                + " */\n"
                + "public class OfflineDataQualityHandler extends IJobHandler {\n"
                + "\n"
                + "  // 数据质量任务projectId\n"
                + "  private String projectId = \"%s\";\n"
                + "\n"
                + "  // 数据质量任务的表Id\n"
                + "  private String tblId = \"%s\";\n"
                + "\n"
                + "  @Autowired private DataQualityTask dataQualityTask;\n"
                + "\n"
                + "  @Override\n"
                + "  public void execute() throws Exception {\n"
                + "    dataQualityTask.run(projectId,tblId);\n"
                + "  }\n"
                + "\n"
                + "}\n";
        return String.format(glueJavaFormat, projectId, tblId);
    }

    @Override
    public Boolean tryRun(Long tblId, Long partitionId, List<Long> ruleIds, Long projectId) {
        DataQualityExecDomain request = new DataQualityExecDomain();
        request.setPartitionId(partitionId);
        request.setProjectId(projectId);
        request.setTblId(tblId);
        request.setRuleIds(ruleIds);

        try {
            dataQualityService.processDataQuality(
                request.getProjectId(),
                request.getTblId(),
                request.getRuleIds(),
                5,
                request.getPartitionId());
        } catch (Exception e) {
            throw new BusinessException("试跑失败 " + e.toString());
        }
        return true;
    }

    @Override
    public void executeDataQualityRule(Long ruleId) {

        List<DataQualityRuleEntity> list =
            dataQualityRuleService.list(
                new LambdaQueryWrapper<>(DataQualityRuleEntity.class)
                    .in(DataQualityRuleEntity::getTblId, ruleId));

        if (CollectionUtils.isEmpty(list)) {
            return;
        }
        DataQualityRuleEntity dataQualityRuleEntity = list.get(0);
        List<Long> longs = list.stream().map(e -> e.getId()).collect(Collectors.toList());

        Boolean b =
            tryRun(
                ruleId,
                dataQualityRuleEntity.getPartitionId(),
                longs,
                dataQualityRuleEntity.getProjectId());
        return;
    }

    public List<DataQualityTableDomain> dataQualityRuleDomainList(Long projectId) {
        List<DataQualityScheduleEntity> list2 =
            dataQualityScheduleService.list(
                new LambdaQueryWrapper<>(DataQualityScheduleEntity.class)
                    .eq(DataQualityScheduleEntity::getStatus, 0)
                    .eq(DataQualityScheduleEntity::getProjectId, projectId));

        for (DataQualityScheduleEntity entity : list2) {
            TblPropEntity tblPropByTblId = tblPropService.getTblPropByTblId(projectId, entity.getTblId());
            entity.setTableName(tblPropByTblId.getTableName());
        }

        List<DataQualityTableDomain> list1 = Convert.toList(DataQualityTableDomain.class, list2);
        return list1;
    }

    @Override
    public Boolean saveRuleTemplates() {
        dataQualityRuleTemplateService.remove(new QueryWrapper<>());
        DataQualityRuleTemplateEntity dataQualityRuleTemplateEntity1 =
            new DataQualityRuleTemplateEntity();
        // 1
        dataQualityRuleTemplateEntity1.setId(1L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.WZX.getId());
        dataQualityRuleTemplateEntity1.setName("完整性约束");
        dataQualityRuleTemplateEntity1.setRemark("通过记录条数和唯一值条数的比值反应数据完整性");
        JSONObject jsonObject1 = new JSONObject();
        jsonObject1.put("Hive", "select count(distinct(${field}))/count(${field}) from ${table}");
        jsonObject1.put("PostgreSQL", "select count(distinct(${field}))/count(${field}) from ${table}");
        jsonObject1.put("Sap", "select count(distinct(${field}))/count(${field}) from ${table}");
        jsonObject1.put("TiDB", "select count(distinct(${field}))/count(${field}) from ${table}");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject1.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(null);
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(
            "[{\"compareType\":0,\"desc\":\"大于\"},{\"compareType\":1,\"desc\":\"大于等于\"},{\"compareType\":2,\"desc\":\"等于\"},{\"compareType\":3,\"desc\":\"小于\"},{\"compareType\":4,\"desc\":\"小于等于\"},{\"compareType\":5,\"desc\":\"不等于\"}]");
        dataQualityRuleTemplateEntity1.setExpectValueTemplate("{\"dataType\":\"number\"}");
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 2
        dataQualityRuleTemplateEntity1.setId(2L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.WZX.getId());
        dataQualityRuleTemplateEntity1.setName("空值约束");
        dataQualityRuleTemplateEntity1.setRemark("检查某个字段是否存在空值");
        JSONObject jsonObject2 = new JSONObject();
        jsonObject2.put("Hive", "select count(1) from ${table}  where ${field} is null");
        jsonObject2.put("PostgreSQL", "select count(1) from ${table}  where ${field} is null");
        jsonObject2.put("Sap", "select count(1) from ${table}  where ${field} is null");
        jsonObject2.put("TiDB", "select count(1) from ${table}  where ${field} is null");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject2.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(null);
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(null);
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 3
        dataQualityRuleTemplateEntity1.setId(3L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.WZX.getId());
        dataQualityRuleTemplateEntity1.setName("空值占比约束");
        dataQualityRuleTemplateEntity1.setRemark("若空值占比波动较大，超过了某个既定值，则可能这个字段的记录出现了问题，信息出现缺失");
        JSONObject jsonObject3 = new JSONObject();
        jsonObject3.put(
            "Hive",
            "select count(case when ${field} is null then 1 else null end)/count(1) from ${table}");
        jsonObject3.put(
            "PostgreSQL",
            "select round(count(case when ${field} is null then 1 else null end)/count(1)::numeric,4) from ${table}");
        jsonObject3.put(
            "Sap",
            "select count(case when ${field} is null then 1 else null end)/count(1) from ${table}");
        jsonObject3.put(
            "TiDB",
            "select count(case when ${field} is null then 1 else null end)/count(1) from ${table}");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject3.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(1);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(null);
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(
            "[{\"compareType\":0,\"desc\":\"大于\"},{\"compareType\":1,\"desc\":\"大于等于\"},{\"compareType\":2,\"desc\":\"等于\"},{\"compareType\":3,\"desc\":\"小于\"},{\"compareType\":4,\"desc\":\"小于等于\"},{\"compareType\":5,\"desc\":\"不等于\"}]");
        dataQualityRuleTemplateEntity1.setExpectValueTemplate("{\"dataType\":\"number\"}");
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 4
        dataQualityRuleTemplateEntity1.setId(4L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.YXX.getId());
        dataQualityRuleTemplateEntity1.setName("取值范围约束");
        dataQualityRuleTemplateEntity1.setRemark("检查对象是否存在超出规定范围的值");
        JSONObject jsonObject4 = new JSONObject();
        jsonObject4.put("Hive", "select concat(min(${field}),',',max(${field})) from ${table}");
        jsonObject4.put("PostgreSQL", "select concat(min(${field}),',',max(${field})) from ${table}");
        jsonObject4.put("Sap", "select concat(min(${field}),concat(',',max(${field}))) from ${table}");
        jsonObject4.put("TiDB", "select concat(min(${field}),',',max(${field})) from ${table}");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject4.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(
            "[{\"name\":\"取值范围\",\"items\":[{\"key\":\"range\",\"placeholder\":\"请输入期望值,若选择两个比较方式,输入两个期望值用逗号隔开\",\"dataType\":\"number\"}]}]");
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(
            "[{\"compareType\":0,\"desc\":\"大于\"},{\"compareType\":1,\"desc\":\"大于等于\"},{\"compareType\":2,\"desc\":\"等于\"},{\"compareType\":3,\"desc\":\"小于\"},{\"compareType\":4,\"desc\":\"小于等于\"},{\"compareType\":5,\"desc\":\"不等于\"}]");
        //    dataQualityRuleTemplateEntity1.setConditionTemplate(
        //
        // "[{\"name\":\"取值范围\",\"items\":[{\"key\":\"min\",\"placeholder\":\"请输入最小值\",\"dataType\":\"number\"},{\"key\":\"max\",\"placeholder\":\"请输入最大值\",\"dataType\":\"number\"}]}]");
        //    dataQualityRuleTemplateEntity1.setCompareTypeTemplate(
        //        "[{\"compareType\":6,\"desc\":\"属于\"},{\"compareType\":7,\"desc\":\"不属于\"}]");
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 5
        dataQualityRuleTemplateEntity1.setId(5L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.YXX.getId());
        dataQualityRuleTemplateEntity1.setName("最大值约束");
        dataQualityRuleTemplateEntity1.setRemark("检查对象是否存在超出最大值的值");
        JSONObject jsonObject5 = new JSONObject();
        jsonObject5.put("Hive", "select max(${field}) from ${table}");
        jsonObject5.put("PostgreSQL", "select max(${field}) from ${table}");
        jsonObject5.put("Sap", "select max(${field}) from ${table}");
        jsonObject5.put("TiDB", "select max(${field}) from ${table}");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject5.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(
            "[{\"name\":\"最大值\",\"items\":[{\"key\":\"max\",\"placeholder\":\"请输入最大值\",\"dataType\":\"number\"}]}]");
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(null);
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 6
        dataQualityRuleTemplateEntity1.setId(6L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.YXX.getId());
        dataQualityRuleTemplateEntity1.setName("最小值约束");
        dataQualityRuleTemplateEntity1.setRemark("检查对象是否存在超出最小值的值");
        JSONObject jsonObject6 = new JSONObject();
        jsonObject6.put("Hive", "select min(${field}) from ${table}");
        jsonObject6.put("PostgreSQL", "select min(${field}) from ${table}");
        jsonObject6.put("Sap", "select min(${field}) from ${table}");
        jsonObject6.put("TiDB", "select min(${field}) from ${table}");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject6.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(
            "[{\"name\":\"最小值\",\"items\":[{\"key\":\"min\",\"placeholder\":\"请输入最小值\",\"dataType\":\"number\"}]}]");
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(null);
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 7
        dataQualityRuleTemplateEntity1.setId(7L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.ZQX.getId());
        dataQualityRuleTemplateEntity1.setName("平均值约束");
        dataQualityRuleTemplateEntity1.setRemark("若整个数据集的某个字段的数据存在错误，利用平均数也可以发现这类问题");
        JSONObject jsonObject7 = new JSONObject();
        jsonObject7.put("Hive", "select avg(${field}) from ${table}");
        jsonObject7.put("PostgreSQL", "select avg(${field}) from ${table}");
        jsonObject7.put("Sap", "select avg(${field}) from ${table}");
        jsonObject7.put("TiDB", "select avg(${field}) from ${table}");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject7.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(
            "[{\"name\":\"平均值\",\"items\":[{\"key\":\"avg\",\"placeholder\":\"请输入平均值\",\"dataType\":\"number\"}]}]");
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(
            "[{\"compareType\":0,\"desc\":\"大于\"},{\"compareType\":1,\"desc\":\"大于等于\"},{\"compareType\":2,\"desc\":\"等于\"},{\"compareType\":3,\"desc\":\"小于\"},{\"compareType\":4,\"desc\":\"小于等于\"},{\"compareType\":5,\"desc\":\"不等于\"}]");
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 8
        dataQualityRuleTemplateEntity1.setId(8L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.ZQX.getId());
        dataQualityRuleTemplateEntity1.setName("总值约束");
        dataQualityRuleTemplateEntity1.setRemark("对数值字段的汇总值是否在某个既定范围内");
        JSONObject jsonObject8 = new JSONObject();
        jsonObject8.put("Hive", "select sum(${field}) from ${table}");
        jsonObject8.put("PostgreSQL", "select sum(${field}) from ${table}");
        jsonObject8.put("Sap", "select sum(${field}) from ${table}");
        jsonObject8.put("TiDB", "select sum(${field}) from ${table}");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject8.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(
            "[{\"name\":\"总值\",\"items\":[{\"key\":\"sum\",\"placeholder\":\"请输入总值\",\"dataType\":\"number\"}]}]");
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(
            "[{\"compareType\":0,\"desc\":\"大于\"},{\"compareType\":1,\"desc\":\"大于等于\"},{\"compareType\":2,\"desc\":\"等于\"},{\"compareType\":3,\"desc\":\"小于\"},{\"compareType\":4,\"desc\":\"小于等于\"},{\"compareType\":5,\"desc\":\"不等于\"}]");
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 9
        dataQualityRuleTemplateEntity1.setId(9L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.YXX.getId());
        dataQualityRuleTemplateEntity1.setName("长度约束");
        dataQualityRuleTemplateEntity1.setRemark("检查对象是否存在超出规定长度的值");
        JSONObject jsonObject9 = new JSONObject();
        jsonObject9.put(
            "Hive", "select concat(min(length(${field})),',',max(length(${field}))) from ${table}");
        jsonObject9.put(
            "PostgreSQL",
            "select concat(min(length(${field})),',',max(length(${field}))) from ${table}");
        jsonObject9.put(
            "Sap",
            "select concat(min(length(${field})),concat(',',max(length(${field})))) from ${table}");
        jsonObject9.put(
            "TiDB", "select concat(min(length(${field})),',',max(length(${field}))) from ${table}");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject9.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(
            "[{\"name\":\"长度\",\"items\":[{\"key\":\"length\",\"placeholder\":\"请输入长度值,若选择两个比较方式,输入两个期望值用逗号隔开\",\"dataType\":\"number\"}]}]");
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(
            "[{\"compareType\":0,\"desc\":\"大于\"},{\"compareType\":1,\"desc\":\"大于等于\"},{\"compareType\":2,\"desc\":\"等于\"},{\"compareType\":3,\"desc\":\"小于\"},{\"compareType\":4,\"desc\":\"小于等于\"},{\"compareType\":5,\"desc\":\"不等于\"}]");
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 10
        dataQualityRuleTemplateEntity1.setId(10L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.YXX.getId());
        dataQualityRuleTemplateEntity1.setName("代码长度约束");
        dataQualityRuleTemplateEntity1.setRemark("当代码长度固定时，检测代码是否有其他长度值");
        JSONObject jsonObject10 = new JSONObject();
        jsonObject10.put("Hive", "select count(1) from ${table} where length(${field}) != ${length}");
        jsonObject10.put(
            "PostgreSQL", "select count(1) from ${table} where length(${field}) != ${length}");
        jsonObject10.put("Sap", "select count(1) from ${table} where length(${field}) != ${length}");
        jsonObject10.put("TiDB", "select count(1) from ${table} where length(${field}) != ${length}");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject10.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(1);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(
            "[{\"name\":\"代码长度\",\"items\":[{\"key\":\"codeLength\",\"placeholder\":\"请输入代码长度值\",\"dataType\":\"number\"}]}]");
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(
            "[{\"compareType\":0,\"desc\":\"大于\"},{\"compareType\":1,\"desc\":\"大于等于\"},{\"compareType\":2,\"desc\":\"等于\"},{\"compareType\":3,\"desc\":\"小于\"},{\"compareType\":4,\"desc\":\"小于等于\"},{\"compareType\":5,\"desc\":\"不等于\"}]");
        dataQualityRuleTemplateEntity1.setExpectValueTemplate("{\"dataType\":\"number\"}");
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 11
        dataQualityRuleTemplateEntity1.setId(11L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.YXX.getId());
        dataQualityRuleTemplateEntity1.setName("正则表达式约束-身份证号验证");
        dataQualityRuleTemplateEntity1.setRemark("身份证号校验(包括第二代18位身份证和第一代15位身份证）");
        JSONObject jsonObject11 = new JSONObject();
        jsonObject11.put(
            "Hive",
            "select count(1) from ${table} where ${field} not rlike '(^[1-9][0-9]{5}19[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9Xx]$)' and ${field} not rlike '(^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}$)'");
        jsonObject11.put(
            "PostgreSQL",
            "select count(1) from ${table} where ${field} !~ '(^\\d{15}$)|(^\\d{18}$)|(^\\d{17}(\\d|X|x)$)'");
        jsonObject11.put(
            "Sap",
            "select count(1) from ${table} where ${field} not like_regexpr '(^\\d{15}$)|(^\\d{18}$)|(^\\d{17}(\\d|X|x)$)'");
        jsonObject11.put(
            "TiDB",
            "select count(1) from ${table} where ${field} not regexp '(^[1-9]\\\\d{5}[1-9]\\\\d{3}((0\\\\d)|(1[0-2]))(([0|1|2]\\\\d)|3[0-1])\\\\d{3}([0-9]|X)$)'");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject11.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(null);
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(null);
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 12
        dataQualityRuleTemplateEntity1.setId(12L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.QT.getId());
        dataQualityRuleTemplateEntity1.setName("日期值约束");
        dataQualityRuleTemplateEntity1.setRemark("检查对象是否存在超出当前日期或指定日期的值");
        JSONObject jsonObject12 = new JSONObject();
        jsonObject12.put("Hive", "select count(1) from ${table} where ${field} ${condition} ${time}");
        jsonObject12.put(
            "PostgreSQL", "select count(1) from ${table} where ${field} ${condition} ${time}");
        jsonObject12.put("Sap", "select count(1) from ${table} where ${field} ${condition} ${time}");
        jsonObject12.put("TiDB", "select count(1) from ${table} where ${field} ${condition} ${time}");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject12.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(1);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(
            "[{\"name\":\"日期值\",\"items\":[{\"key\":\"date\",\"placeholder\":\"请选择日期值\",\"dataType\":\"date\"}]}]");
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(
            "[{\"compareType\":0,\"desc\":\"大于\"},{\"compareType\":1,\"desc\":\"大于等于\"},{\"compareType\":2,\"desc\":\"等于\"},{\"compareType\":3,\"desc\":\"小于\"},{\"compareType\":4,\"desc\":\"小于等于\"},{\"compareType\":5,\"desc\":\"不等于\"}]");
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(0);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 13
        dataQualityRuleTemplateEntity1.setId(13L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.YXX.getId());
        dataQualityRuleTemplateEntity1.setName("枚举值约束");
        dataQualityRuleTemplateEntity1.setRemark("检查对象的值是否满足枚举值内");
        JSONObject jsonObject13 = new JSONObject();
        jsonObject13.put(
            "Hive", "select count(1) from ${table} where ${field} ${condition} (${enums})");
        jsonObject13.put(
            "PostgreSQL", "select count(1) from ${table} where ${field} ${condition} (${enums})");
        jsonObject13.put("Sap", "select count(1) from ${table} where ${field} ${condition} (${enums})");
        jsonObject13.put(
            "TiDB", "select count(1) from ${table} where ${field} ${condition} (${enums})");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject13.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(1);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(
            "[{\"name\":\"枚举值\",\"items\":[{\"key\":\"enum\",\"placeholder\":\"请输入枚举值，以逗号隔开\",\"dataType\":\"text\"}]}]");
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(
            "[{\"compareType\":6,\"desc\":\"属于\"},{\"compareType\":7,\"desc\":\"不属于\"}]");
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 14
        dataQualityRuleTemplateEntity1.setId(14L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.WZX.getId());
        dataQualityRuleTemplateEntity1.setName("空字符串约束");
        dataQualityRuleTemplateEntity1.setRemark("检查对象的值是否存在空字符串");
        JSONObject jsonObject14 = new JSONObject();
        jsonObject14.put("Hive", "select count(1) from ${table} where ${field} = ''");
        jsonObject14.put("PostgreSQL", "select count(1) from ${table} where ${field} = ''");
        jsonObject14.put("Sap", "select count(1) from ${table} where ${field} = ''");
        jsonObject14.put("TiDB", "select count(1) from ${table} where ${field} = ''");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject14.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(null);
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(null);
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 15
        dataQualityRuleTemplateEntity1.setId(15L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.QT.getId());
        dataQualityRuleTemplateEntity1.setName("空值或空字符串约束");
        dataQualityRuleTemplateEntity1.setRemark("检查对象是否存在空值或空字符二者之一");
        JSONObject jsonObject15 = new JSONObject();
        jsonObject15.put(
            "Hive", "select count(1) from ${table} where ${field} = '' or ${field} is null");
        jsonObject15.put(
            "PostgreSQL", "select count(1) from ${table} where ${field} = '' or ${field} is null");
        jsonObject15.put(
            "Sap", "select count(1) from ${table} where ${field} = '' or ${field} is null");
        jsonObject15.put(
            "TiDB", "select count(1) from ${table} where ${field} = '' or ${field} is null");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject15.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(null);
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(null);
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(0);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 16
        dataQualityRuleTemplateEntity1.setId(16L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.YXX.getId());
        dataQualityRuleTemplateEntity1.setName("特殊字符约束");
        dataQualityRuleTemplateEntity1.setRemark("检查对象是否存在特殊字符");
        JSONObject jsonObject16 = new JSONObject();
        jsonObject16.put("Hive", "select count(1) from ${table} where ${rlike}");
        jsonObject16.put("PostgreSQL", "select count(1) from ${table} where ${like}");
        jsonObject16.put("Sap", "select count(1) from ${table} where ${like}");
        jsonObject16.put("TiDB", "select count(1) from ${table} where ${like}");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject16.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(
            "[{\"name\":\"特殊字符\",\"items\":[{\"key\":\"specialChar\",\"placeholder\":\"请输入特殊字符\",\"dataType\":\"text\"}]}]");
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(
            "[{\"compareType\":6,\"desc\":\"属于\"},{\"compareType\":7,\"desc\":\"不属于\"}]");
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 17
        dataQualityRuleTemplateEntity1.setId(17L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.WYX.getId());
        dataQualityRuleTemplateEntity1.setName("重复值约束");
        dataQualityRuleTemplateEntity1.setRemark("检查对象是否存在重复值");
        JSONObject jsonObject17 = new JSONObject();
        jsonObject17.put(
            "Hive",
            "select count(1) from (select ${field},count(1) from ${table} group by ${field} having count(1) > 1) t");
        jsonObject17.put(
            "PostgreSQL",
            "select count(1) from (select ${field},count(1) from ${table} group by ${field} having count(1) > 1) t");
        jsonObject17.put(
            "Sap",
            "select count(1) from (select ${field},count(1) from ${table} group by ${field} having count(1) > 1) t");
        jsonObject17.put(
            "TiDB",
            "select count(1) from (select ${field},count(1) from ${table} group by ${field} having count(1) > 1) t");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject17.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(null);
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(null);
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 18
        dataQualityRuleTemplateEntity1.setId(18L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.QT.getId());
        dataQualityRuleTemplateEntity1.setName("及时性约束-天级别");
        dataQualityRuleTemplateEntity1.setRemark("检查表数据是否存在延迟");
        JSONObject jsonObject18 = new JSONObject();
        jsonObject18.put(
            "Hive",
            "select count(1) from ${table} where datediff(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'),`${field}`) >=1");
        jsonObject18.put(
            "PostgreSQL",
            "select count(1) from ${table} where (extract(day from now() - ${field})>=1)");
        jsonObject18.put(
            "Sap", "select count(1) from ${table} where (DAYS_BETWEEN(${field},NOW()) >=1)");
        jsonObject18.put(
            "TiDB", "select count(1) from ${table} where (TIMESTAMPDIFF(DAY,${field},NOW()) >=1)");
        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject18.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(null);
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(null);
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(0);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 19
        dataQualityRuleTemplateEntity1.setId(19L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.YXX.getId());
        dataQualityRuleTemplateEntity1.setName("正则表达式约束-电话号码验证");
        dataQualityRuleTemplateEntity1.setRemark("电话号码验证(包括国际固定电话和手机号码)");
        JSONObject jsonObject19 = new JSONObject();
        jsonObject19.put(
            "Hive",
            "select count(1) from ${table} where ${field} not rlike '^(((\\\\+\\\\d{2}-)?0\\\\d{2,3}-\\\\d{7,8}))'  and ${field} not rlike '(^1[0-9]{10}$)'");
        jsonObject19.put(
            "PostgreSQL",
            "select count(1) from ${table} where ${field} !~ '(^1[3|4|5|6|7|8][0-9]\\d{8}$)'");
        jsonObject19.put(
            "Sap",
            "select count(1) from ${table} where ${field} not like_regexpr '(^1[3|4|5|6|7|8][0-9]\\d{8}$)'");
        jsonObject19.put(
            "TiDB",
            "select count(1) from ${table} where ${field} not regexp '(^1[3|4|5|6|7|8][0-9]\\d{8}$)'");

        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject19.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(null);
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(null);
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(1);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);
        // 20
        dataQualityRuleTemplateEntity1.setId(20L);
        dataQualityRuleTemplateEntity1.setCategory(DataQualityCategoryEnum.QT.getId());
        dataQualityRuleTemplateEntity1.setName("及时性约束-小时级别");
        dataQualityRuleTemplateEntity1.setRemark("检查表数据是否存在延迟");
        JSONObject jsonObject20 = new JSONObject();
        jsonObject20.put(
            "Hive",
            "select count(1) from ${table} where (hour(current_timestamp())-hour(`${field}`)+(datediff(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'), `${field}`))*24) >= 1");
        jsonObject20.put(
            "PostgreSQL",
            "select count(1) from ${table} where (extract(hour from now() - ${field})>=1)");
        jsonObject20.put(
            "Sap", "select count(1) from ${table} where ((SECONDS_BETWEEN(${field},NOW())/3600) >=1)");
        jsonObject20.put(
            "TiDB", "select count(1) from ${table} where (TIMESTAMPDIFF(HOUR,${field},NOW()) >=1)");

        dataQualityRuleTemplateEntity1.setSqlTemplate(jsonObject20.toJSONString());
        dataQualityRuleTemplateEntity1.setTemplateType(0);
        dataQualityRuleTemplateEntity1.setTemplateTableType(0);
        dataQualityRuleTemplateEntity1.setConditionTemplate(null);
        dataQualityRuleTemplateEntity1.setCompareTypeTemplate(null);
        dataQualityRuleTemplateEntity1.setExpectValueTemplate(null);
        dataQualityRuleTemplateEntity1.setRowState(0);
        dataQualityRuleTemplateService.save(dataQualityRuleTemplateEntity1);

        return true;
    }

    @Override
    public List<DataQualityRuleDetailDomain> getListByPage(
        Long projectId, Integer pageSize, Integer pageNum) {

        Page<DataQualityRuleEntity> result =
            dataQualityRuleService.page(
                new Page<>(pageNum, pageSize),
                new LambdaQueryWrapper<DataQualityRuleEntity>()
                    .eq(DataQualityRuleEntity::getProjectId, projectId));

        return ConvertUtil.copyProperties(result.getRecords(), DataQualityRuleDetailDomain.class);
    }
}
